{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#导入模块\n",
    "import pandas as pd\n",
    "import pymysql\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#建立链接\n",
    "conn1 = pymysql.Connect(host = 'localhost',port = 3306,user = 'root',password = '159357',database = 'recommend2',charset = 'utf8')\n",
    "conn2 = create_engine('mysql+pymysql://root:159357@localhost:3306/recommend2?charset=utf8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#构建表结构\n",
    "'''\n",
    "create table status( \n",
    "    id int primary key auto_increment, \n",
    "    content varchar(20)\n",
    ");\n",
    "\n",
    "create table category(\n",
    "    id int primary key auto_increment,\n",
    "    content varchar(100) not null\n",
    ");\n",
    "\n",
    "create table customer (\n",
    "    id int primary key auto_increment,\n",
    "    tel varchar(32),\n",
    "    category_id int,\n",
    "    constraint c1 foreign key (category_id) references category(id) on delete cascade\n",
    ");\n",
    "\n",
    "create table salesstaff(\n",
    "    id int primary key auto_increment,\n",
    "    name varchar(16),\n",
    "    account varchar(16) not null unique ,\n",
    "    password varchar(32) not null\n",
    ");\n",
    "\n",
    "create table mission(\n",
    "    id int primary key auto_increment,\n",
    "    customer_id int not null ,\n",
    "    salesstaff_id int not null,\n",
    "    createDate date not null,\n",
    "    status_id int not null,\n",
    "    constraint c2 foreign key (customer_id) references customer(id) on delete cascade ,\n",
    "    constraint c3 foreign key (salesstaff_id) references salesstaff(id) on delete cascade ,\n",
    "    constraint c4 foreign key (status_id) references status(id) on delete cascade\n",
    "\n",
    ");\n",
    "'''\n",
    "\n",
    "sql_status = 'create table status( id int primary key auto_increment, content varchar(20));'\n",
    "sql_category = 'create table category( id int primary key auto_increment,content varchar(100) not null);'\n",
    "sql_customer = 'create table customer ( id int primary key auto_increment, tel varchar(32), category_id int, constraint c1 foreign key (category_id) references category(id) on delete cascade);'\n",
    "sql_salesstaff = 'create table salesstaff( id int primary key auto_increment, name varchar(16), account varchar(16) not null unique , password varchar(32) not null );'\n",
    "sql_mission = 'create table mission( id int primary key auto_increment, customer_id int not null , salesstaff_id int not null, createDate date not null, status_id int not null, constraint c2 foreign key (customer_id) references customer(id) on delete cascade , constraint c3 foreign key (salesstaff_id) references salesstaff(id) on delete cascade , constraint c4 foreign key (status_id) references status(id) on delete cascade );'\n",
    "\n",
    "cs = conn1.cursor()\n",
    "cs.execute(sql_status)\n",
    "cs.execute(sql_category)\n",
    "cs.execute(sql_customer)\n",
    "cs.execute(sql_salesstaff)\n",
    "cs.execute(sql_mission)\n",
    "conn1.commit()\n",
    "cs.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#销售表同步到数据库\n",
    "salesstaff = pd.read_excel('./数据/销售人员.xlsx',sheet_name = '销售信息')\n",
    "salesstaff.head()\n",
    "salesstaff.name = salesstaff.name.apply(lambda name : name.strip())\n",
    "salesstaff.to_sql('salesstaff',conn2,index = False,if_exists = 'append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#类别表同步到数据库\n",
    "categorys = pd.ExcelFile('./数据/顾客信息.xlsx').sheet_names\n",
    "values = []\n",
    "for i in categorys:\n",
    "    values.append(\"('%s')\" % i)\n",
    "sql = 'insert into category (content) values %s;' % ','.join(values)\n",
    "cs = conn1.cursor()\n",
    "cs.execute(sql)\n",
    "conn1.commit()\n",
    "cs.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#顾客表同步到数据库\n",
    "customer = pd.read_excel('./数据/顾客信息.xlsx',sheet_name = None)\n",
    "# type(customer)\n",
    "#1.对所有的顾客添加所属行业\n",
    "for key in customer:\n",
    "    customer[key]['种类'] = key\n",
    "customer\n",
    "#2.将所有顾客拼接成一张表\n",
    "customer = pd.concat([customer[key] for key in customer.keys()],axis = 0)\n",
    "customer\n",
    "#3.读取category表信息并转化成字典\n",
    "category_id_content = pd.read_sql('select id,content from category',conn2)\n",
    "category_id_content\n",
    "category_id_content.index = category_id_content.content.values\n",
    "category_id_content\n",
    "category_id_content_to_dict = category_id_content.to_dict()['id']\n",
    "category_id_content_to_dict\n",
    "#4.对所有顾客种类列进行替换\n",
    "customer['种类'] = customer['种类'].apply(lambda x : category_id_content_to_dict[x])\n",
    "customer.columns = ['tel','category_id']\n",
    "customer\n",
    "customer.to_sql('customer',conn2,index = False,if_exists = 'append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#状态表同步到数据库\n",
    "mission = pd.read_excel('./数据/2020-06月销售任务清单.xlsx',sheet_name=None)\n",
    "for i in mission:\n",
    "    mission[i]['createDate'] = i\n",
    "mission = pd.concat([mission[key] for key in mission.keys()],axis = 0)\n",
    "status = mission['沟通结果'].unique().tolist()\n",
    "status = pd.DataFrame(status,columns = ['content'])\n",
    "status.to_sql('status',conn2,index = False,if_exists = 'append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#任务表同步到数据库\n",
    "#从customer表读取id和tel\n",
    "customer_id_tel = pd.read_sql('select id,tel from customer',conn2)\n",
    "customer_id_tel.index = customer_id_tel.tel.values\n",
    "customer_id_tel_to_dict = customer_id_tel.to_dict()['id']\n",
    "#从salesstaff表读取id和name\n",
    "salesstaff_id_name = pd.read_sql('select id,name from salesstaff',conn2)\n",
    "salesstaff_id_name.index = salesstaff_id_name.name.values\n",
    "salesstaff_id_name_to_dict = salesstaff_id_name.to_dict()['id']\n",
    "#从status表读取id和content\n",
    "status_id_content = pd.read_sql('select id,content from status',conn2)\n",
    "status_id_content.index = status_id_content.content.values\n",
    "status_id_content_to_dict = status_id_content.to_dict()['id']\n",
    "#修正mission表列名\n",
    "mission.columns = ['customer_id','salesstaff_id','status_id','createDate']\n",
    "#用id替换mission表中相关数值\n",
    "mission.customer_id = mission.customer_id.apply(lambda x : customer_id_tel_to_dict[x])\n",
    "mission.salesstaff_id = mission.salesstaff_id.apply(lambda x : salesstaff_id_name_to_dict[x])\n",
    "mission.status_id = mission.status_id.apply(lambda x : status_id_content_to_dict[x])\n",
    "#将最终的mission表存入到数据库\n",
    "mission.to_sql('mission',conn2,index = False,if_exists = 'append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
